package cn.buk.flight.dao;

import cn.buk.common.sc.CommonSearchCriteria;
import cn.buk.common.sc.Page;
import cn.buk.flight.entity.AirlineTable;
import cn.buk.flight.entity.AirlineTable_;
import cn.buk.flight.entity.FareDisplay;
import cn.buk.flight.entity.FdCacheInfo;
import cn.buk.flight.entity.FdRequest;
import cn.buk.flight.entity.FdRequest_;
import cn.buk.flight.entity.FdResult;
import cn.buk.flight.entity.FdResult_;
import cn.buk.flight.entity.FlightInfo;
import cn.buk.flight.entity.FlightInfoCache;
import cn.buk.flight.entity.FlightInfo_;
import cn.buk.flight.entity.FlightPrice;
import cn.buk.flight.entity.NfdRequest;
import cn.buk.flight.entity.NfdResult;
import cn.buk.flight.entity.NfdResult_;
import cn.buk.flight.entity.RouteInfo;
import cn.buk.flight.entity.SkInfo;
import cn.buk.common.util.DateUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author yfdai
 */
@Component
@Transactional("flightTx")
public class FlightDaoImpl extends AbstractDao implements FlightDao {

  public static final Logger logger = LogManager.getLogger(FlightDaoImpl.class);


  @Override
  public List<FlightInfo> retrieveFlightInfo(String dcity, String acity, Date ddate, String carrier,
      String opCarrier, int codeShared, Page page, int minId) {
    List<FlightInfo> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<FlightInfo> cq = cb.createQuery(FlightInfo.class);
    Root<FlightInfo> root = cq.from(FlightInfo.class);

    Predicate where = cb.conjunction();

    if (dcity != null && dcity.trim().toUpperCase().length() == 3) {
      where = cb.and(where, cb.equal(root.get(FlightInfo_.depPort), dcity));
    }
    if (acity != null && acity.trim().toUpperCase().length() == 3) {
      where = cb.and(where, cb.equal(root.get(FlightInfo_.arrPort), acity));
    }
    if (carrier != null && carrier.trim().toUpperCase().length() == 2) {
      where = cb.and(where, cb.like(root.get(FlightInfo_.flightNo), carrier + "%"));
    }
    if (ddate != null) {
      where = cb.and(where, cb.equal(root.get(FlightInfo_.depDate), ddate));
    }

    if (codeShared >= 0) {
      where = cb
          .and(where, cb.equal(root.get(FlightInfo_.codeShared), Integer.toString(codeShared)));
    }
    if (opCarrier != null && opCarrier.trim().length() == 2) {
      where = cb.and(where, cb.like(root.get(FlightInfo_.carrierFlightNo), opCarrier + "%"));
    }
    if (minId > 0) {
      where = cb.and(where, cb.greaterThan(root.get(FlightInfo_.id), minId));
    }

    cq.where(where);
    List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
    if (minId > 0) {
      orderByes.add(cb.asc(root.get("id")));
    } else {
      orderByes.add(cb.asc(root.get("depDate")));
      orderByes.add(cb.asc(root.get("depCity")));
      orderByes.add(cb.asc(root.get("arrCity")));
      orderByes.add(cb.asc(root.get("depTime")));
    }
//            orderByes.add(cb.desc(root.get("pclassCount")));
//            orderByes.add(cb.desc(root.get("fclassCount")));

    cq.orderBy(orderByes);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<FlightInfo> countRoot = countQuery.from(FlightInfo.class);

    Predicate where0 = cb.conjunction();
    if (dcity != null && dcity.trim().toUpperCase().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FlightInfo_.depPort), dcity));
    }
    if (acity != null && acity.trim().toUpperCase().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FlightInfo_.arrPort), acity));
    }
    if (carrier != null && carrier.trim().toUpperCase().length() == 2) {
      where0 = cb.and(where0, cb.like(countRoot.get(FlightInfo_.flightNo), carrier + "%"));
    }
    if (ddate != null) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FlightInfo_.depDate), ddate));
    }
    if (codeShared >= 0) {
      where0 = cb.and(where0,
          cb.equal(countRoot.get(FlightInfo_.codeShared), Integer.toString(codeShared)));
    }
    if (opCarrier != null && opCarrier.trim().length() == 2) {
      where0 = cb.and(where0, cb.like(countRoot.get(FlightInfo_.carrierFlightNo), opCarrier + "%"));
    }
    if (minId > 0) {
      where0 = cb.and(where0, cb.greaterThan(countRoot.get(FlightInfo_.id), minId));
    }

    countQuery.select(cb.count(countRoot)).where(where0);
    Long count = em.createQuery(countQuery).getSingleResult();

    int maxResults = count.intValue();
    if (maxResults > 0) {
      //Page page = sc.getPage();
      page.setRowCount(maxResults);
      int i = (page.getPageNo() - 1) * page.getPageSize();

      if (i < 0) {
        i = 0;
      }

      results = em.createQuery(cq)
          .setFirstResult(i)
          .setMaxResults(page.getPageSize()).getResultList();

    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public FlightPrice getFlightPrice(int id) {
    return em.find(FlightPrice.class, id);
  }


  @Override
  public int createFdRequests(List<FdRequest> list) {
    for (FdRequest info : list) {
      em.persist(info);
    }

    return 1;
  }

  @Override
  public int createNfdRequest(int enterpriseId, String carrier, String dport, String aport) {
    NfdRequest request = new NfdRequest();
    request.setEnterpriseId(enterpriseId);
    request.setCarrier(carrier);
    request.setDport(dport);
    request.setAport(aport);

    em.persist(request);

    return 1;
  }

  @Override
  public List<FdResult> searchFdResult(int enterpriseId, CommonSearchCriteria sc) {
    List<FdResult> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<FdResult> cq = cb.createQuery(FdResult.class);
    Root<FdResult> root = cq.from(FdResult.class);
    root.alias("o");

    Predicate where = cb.conjunction();
    where = cb.and(where, cb.equal(root.get(FdResult_.enterpriseId), enterpriseId));

    if (sc.getCarrier().length() == 2) {
      where = cb.and(where, cb.equal(root.get(FdResult_.carrier), sc.getCarrier()));
    }
    if (sc.getDport().length() == 3) {
      where = cb.and(where, cb.equal(root.get(FdResult_.dport), sc.getDport()));
    }
    if (sc.getAport().length() == 3) {
      where = cb.and(where, cb.equal(root.get(FdResult_.aport), sc.getAport()));
    }
    if (sc.getSubclass().length() == 1) {
      where = cb.and(where, cb.equal(root.get(FdResult_.subclass), sc.getSubclass()));
    }
    cq.where(where);

    List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("carrier")));
    orderByes.add(cb.asc(root.get("dport")));
    orderByes.add(cb.asc(root.get("aport")));
    orderByes.add(cb.desc(root.get("owPrice")));
    orderByes.add(cb.desc(root.get("rtPrice")));
    cq.orderBy(orderByes);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<FdResult> countRoot = countQuery.from(FdResult.class);
    countRoot.alias("c");

    Predicate where0 = cb.conjunction();
    where0 = cb.and(where0, cb.equal(countRoot.get(FdResult_.enterpriseId), enterpriseId));

    if (sc.getCarrier() != null && sc.getCarrier().length() == 2) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdResult_.carrier), sc.getCarrier()));
    }
    if (sc.getDport() != null && sc.getDport().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdResult_.dport), sc.getDport()));
    }
    if (sc.getAport() != null && sc.getAport().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdResult_.aport), sc.getAport()));
    }
    if (sc.getSubclass().length() == 1) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdResult_.subclass), sc.getSubclass()));
    }

    countQuery.select(cb.count(countRoot)).where(where0);
    Long count = em.createQuery(countQuery).getSingleResult();

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);
      int i = (page.getPageNo() - 1) * page.getPageSize();

      if (i < 0) {
        i = 0;
      }

      results = em.createQuery(cq)
          .setFirstResult(i)
          .setMaxResults(page.getPageSize()).getResultList();

    }

    return results == null ? new ArrayList<FdResult>() : results;
  }

  @Override
  public List<NfdResult> searchNfdResult(int enterpriseId, CommonSearchCriteria sc) {
    List<NfdResult> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<NfdResult> cq = cb.createQuery(NfdResult.class);
    Root<NfdResult> root = cq.from(NfdResult.class);
    root.alias("o");

    Predicate where = cb.conjunction();
    where = cb.and(where, cb.equal(root.get(NfdResult_.enterpriseId), enterpriseId));

    if (sc.getCarrier().length() == 2) {
      where = cb.and(where, cb.equal(root.get(NfdResult_.carrier), sc.getCarrier()));
    }
    if (sc.getDport().length() == 3) {
      where = cb.and(where, cb.equal(root.get(NfdResult_.dport), sc.getDport()));
    }
    if (sc.getAport().length() == 3) {
      where = cb.and(where, cb.equal(root.get(NfdResult_.aport), sc.getAport()));
    }
    if (sc.getSubclass().length() == 1) {
      where = cb.and(where, cb.equal(root.get(NfdResult_.subclass), sc.getSubclass()));
    }
    cq.where(where);

    List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("carrier")));
    orderByes.add(cb.asc(root.get("dport")));
    orderByes.add(cb.asc(root.get("aport")));
    orderByes.add(cb.desc(root.get("owPrice")));
    orderByes.add(cb.desc(root.get("rtPrice")));
    cq.orderBy(orderByes);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<NfdResult> countRoot = countQuery.from(NfdResult.class);
    countRoot.alias("c");

    Predicate where0 = cb.conjunction();
    where0 = cb.and(where0, cb.equal(countRoot.get(NfdResult_.enterpriseId), enterpriseId));

    if (sc.getCarrier() != null && sc.getCarrier().length() == 2) {
      where0 = cb.and(where0, cb.equal(countRoot.get(NfdResult_.carrier), sc.getCarrier()));
    }
    if (sc.getDport() != null && sc.getDport().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(NfdResult_.dport), sc.getDport()));
    }
    if (sc.getAport() != null && sc.getAport().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(NfdResult_.aport), sc.getAport()));
    }
    if (sc.getSubclass().length() == 1) {
      where0 = cb.and(where0, cb.equal(countRoot.get(NfdResult_.subclass), sc.getSubclass()));
    }

    countQuery.select(cb.count(countRoot)).where(where0);
    Long count = em.createQuery(countQuery).getSingleResult();

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);
      int i = (page.getPageNo() - 1) * page.getPageSize();

      if (i < 0) {
        i = 0;
      }

      results = em.createQuery(cq)
          .setFirstResult(i)
          .setMaxResults(page.getPageSize()).getResultList();

    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<FdRequest> searchFdRequest(int enterpriseId, CommonSearchCriteria sc) {
    List<FdRequest> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<FdRequest> cq = cb.createQuery(FdRequest.class);
    Root<FdRequest> root = cq.from(FdRequest.class);

    Predicate where = cb.conjunction();
    where = cb.and(where, cb.equal(root.get(FdRequest_.enterpriseId), enterpriseId));

    if (sc.getCarrier().length() == 2) {
      where = cb.and(where, cb.equal(root.get(FdRequest_.carrier), sc.getCarrier()));
    }
    if (sc.getDport().length() == 3) {
      where = cb.and(where, cb.equal(root.get(FdRequest_.dport), sc.getDport()));
    }
    if (sc.getAport().length() == 3) {
      where = cb.and(where, cb.equal(root.get(FdRequest_.aport), sc.getAport()));
    }

    if (sc.getStatus() >= 0) {
      where = cb.and(where, cb.equal(root.get(FdRequest_.status), sc.getStatus()));
    }

    cq.where(where);
    List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
    orderByes.add(cb.desc(root.get("triesCount")));
    orderByes.add(cb.asc(root.get("dport")));
    orderByes.add(cb.asc(root.get("aport")));

    cq.orderBy(orderByes);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<FdRequest> countRoot = countQuery.from(FdRequest.class);

    Predicate where0 = cb.conjunction();
    where0 = cb.and(where0, cb.equal(countRoot.get(FdRequest_.enterpriseId), enterpriseId));

    if (sc.getCarrier().length() == 2) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdRequest_.carrier), sc.getCarrier()));
    }
    if (sc.getDport().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdRequest_.dport), sc.getDport()));
    }
    if (sc.getAport().length() == 3) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdRequest_.aport), sc.getAport()));
    }
    if (sc.getStatus() >= 0) {
      where0 = cb.and(where0, cb.equal(countRoot.get(FdRequest_.status), sc.getStatus()));
    }

    countQuery.select(cb.count(countRoot)).where(where0);
    Long count = em.createQuery(countQuery).getSingleResult();

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);
      int i = (page.getPageNo() - 1) * page.getPageSize();

      if (i < 0) {
        i = 0;
      }

      results = em.createQuery(cq)
          .setFirstResult(i)
          .setMaxResults(page.getPageSize()).getResultList();

    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public int deleteFdRequest(int enterpriseId, int id) {
    return em
        .createQuery("delete from FdRequest o where o.enterpriseId = :enterpriseId and o.id = :id")
        .setParameter("enterpriseId", enterpriseId)
        .setParameter("id", id)
        .executeUpdate();
  }

  @Override
  public List<NfdRequest> searchNfdRequestWaitList(int max) {
    return em
        .createQuery("select o from NfdRequest o where o.status = :status order by o.triesCount",
            NfdRequest.class)
        .setParameter("status", 0)
        .setFirstResult(0)
        .setMaxResults(max)
        .getResultList();
  }

  @Override
  public int setFdRequestDone(int enterpriseId, int id, String remark) {
    return em.createQuery(
        "update FdRequest o set o.remark = :remark, o.checkTime = :checkTime, o.status = :status where o.enterpriseId = :enterpriseId and o.id = :id")
        .setParameter("enterpriseId", enterpriseId)
        .setParameter("id", id)
        .setParameter("remark", remark)
        .setParameter("checkTime", DateUtil.getCurDateTime())
        .setParameter("status", 1)
        .executeUpdate();
  }

  @Override
  public int setNfdRequestDone(int enterpriseId, int id, String remark) {
    return em.createQuery(
        "update NfdRequest o set o.remark = :remark, o.checkTime = :checkTime, o.status = :status where o.enterpriseId = :enterpriseId and o.id = :id")
        .setParameter("enterpriseId", enterpriseId)
        .setParameter("id", id)
        .setParameter("remark", remark)
        .setParameter("checkTime", DateUtil.getCurDateTime())
        .setParameter("status", 1)
        .executeUpdate();
  }

  /**
   * 删除全部FD请求（已完成的和创建时间早于指定时间且未完成的）
   *
   * @param enterpriseId 企业
   * @param minDateTime 指定时间
   */
  @Override
  public int deleteAllFdRequest(int enterpriseId, Date minDateTime) {
    return em.createQuery(
        "delete from FdRequest o where o.enterpriseId = :enterpriseId and (o.createTime <= :minDateTime or o.status = 1) ")
        .setParameter("enterpriseId", enterpriseId)
        .setParameter("minDateTime", minDateTime)
        .executeUpdate();
  }

  @Override
  public int deleteAllFdResult(int enterpriseId) {
    return em.createQuery(
        "delete from FdResult o where o.enterpriseId = :enterpriseId and o.lastupdate < :today")
        .setParameter("enterpriseId", enterpriseId)
        .setParameter("today", DateUtil.getCurDate())
        .executeUpdate();
  }


  /**
   * 搜索航空公司飞的所有 航线
   */
  @Override
  public List<AirlineTable> searchAirline(String carrier) {
    if (carrier != null) {
      return em.createQuery("select o from AirlineTable o where o.carrier = :carrier",
          AirlineTable.class)
          .setParameter("carrier", carrier)
          .getResultList();
    } else {
      return em.createQuery("select o from AirlineTable o", AirlineTable.class)
          .getResultList();
    }
  }


  @Override
  public int updateAirlineFlightCount(String carrier, String dport, String aport, int flightCount) {
    return em.createQuery("update AirlineTable o set o.flightCount = :flightCount " +
        " where o.carrier = :carrier and o.dport = :dport and o.aport = :aport and o.flightCount < :flightCount")
        .setParameter("carrier", carrier)
        .setParameter("dport", dport)
        .setParameter("aport", aport)
        .setParameter("flightCount", flightCount)
        .executeUpdate();
  }


  @Override
  public List<AirlineTable> searchAirline(String carrier, String dport, String aport,
      int flightCount) {
    List<AirlineTable> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<AirlineTable> cq = cb.createQuery(AirlineTable.class);
    Root<AirlineTable> root = cq.from(AirlineTable.class);

    Predicate where = cb.conjunction();

    if (carrier.length() == 2) {
      where = cb.and(where, cb.equal(root.get(AirlineTable_.carrier), carrier));
    }
    if (dport.length() == 3) {
      where = cb.and(where, cb.equal(root.get(AirlineTable_.dport), dport));
    }
    if (aport.length() == 3) {
      where = cb.and(where, cb.equal(root.get(AirlineTable_.aport), aport));
    }
    if (flightCount > 0) {
      where = cb.and(where, cb.equal(root.get(AirlineTable_.flightCount), flightCount));
    }

    cq.where(where);
    List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("dport")));
    orderByes.add(cb.asc(root.get("aport")));

    cq.orderBy(orderByes);

    results = em.createQuery(cq)
        .getResultList();

    return results == null ? new ArrayList<>() : results;
  }

  @Override
  public int createFd(FareDisplay fd) {
    em.persist(fd);
    return fd.getId();
  }

  @Override
  public FlightInfoCache searchFlightInfoCache(String dcity, String acity, Date ddate) {
    List<FlightInfoCache> results = em.createQuery(
        "select o from FlightInfoCache o "
            + "where o.dcity = :dcity and o.acity = :acity and o.ddate = :ddate", FlightInfoCache.class)
        .setParameter("dcity", dcity)
        .setParameter("acity", acity)
        .setParameter("ddate", ddate)
        .getResultList();

    if (results.size() == 1) {
      return results.get(0);
    } else {
      return null;
    }
  }

  @Override
  public List<SkInfo> searchAirlineSchedules(CommonSearchCriteria sc) {
    List<SkInfo> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<SkInfo> cq = cb.createQuery(SkInfo.class);
    Root<SkInfo> root = cq.from(SkInfo.class);
    root.alias("o");

    List<Predicate> predicates = new ArrayList<>();

    Predicate where = cb.conjunction();
    predicates.add(where);

    // 航班号
    if (sc.getCarrier() != null && sc.getCarrier().length() > 2) {
      where = cb.equal(root.get("flightNo"), sc.getCarrier().trim().toUpperCase());
      predicates.add(where);
    }

    if (sc.getDport() != null && sc.getDport().trim().length() == 3) {
      where = cb.equal(root.get("dcity"), sc.getDport().trim().toUpperCase());
      predicates.add(where);
    }

    if (sc.getAport() != null && sc.getAport().trim().length() == 3) {
      where = cb.equal(root.get("acity"), sc.getAport().trim().toUpperCase());
      predicates.add(where);
    }

    cq.where(predicates.toArray(new Predicate[predicates.size()]));
    List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("dcity")));
    orderByes.add(cb.asc(root.get("acity")));
    orderByes.add(cb.asc(root.get("dtime")));
    orderByes.add(cb.asc(root.get("atime")));

    cq.orderBy(orderByes);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<SkInfo> countRoot = countQuery.from(SkInfo.class);
    countRoot.alias("o");

    countQuery.select(cb.count(countRoot))
        .where(predicates.toArray(new Predicate[predicates.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);

      results = em.createQuery(cq)
          .setFirstResult(page.getFirstPosition())
          .setMaxResults(page.getPageSize()).getResultList();

    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }


  @Override
  @SuppressWarnings("unchecked")
  public List<FdCacheInfo> searchFdCache(String depPort, String arrPort) {
    List<FdCacheInfo> fdCacheList = null;

    Query query;
    if (depPort == null || depPort.length() != 3) {
      query = em.createQuery("select f from FdCacheInfo f "
          + "  order by f.depPort, f.arrPort");
    } else if (arrPort == null || arrPort.length() != 3) {
      query = em.createQuery("select f from FdCacheInfo f "
          + " where f.depPort = ?1 "
          + "  order by f.depPort, f.arrPort")
          .setParameter(1, depPort);
    } else {
      query = em.createQuery("select f from FdCacheInfo f "
          + " where f.depPort = ?1 and f.arrPort = ?2  "
          + "  order by f.id")
          .setParameter(1, depPort)
          .setParameter(2, arrPort);
    }
    fdCacheList = query.getResultList();

    return fdCacheList;
  }

  @Override
  public List<RouteInfo> searchRoute(String depPort, String arrPort) {
    List<RouteInfo> routeList = null;

    routeList = searchRoute(depPort, arrPort, em);
    return routeList;
  }

  @SuppressWarnings("unchecked")
  private List<RouteInfo> searchRoute(String depPort, String arrPort, EntityManager em) {
    List<RouteInfo> routeList = null;

    Query query;
    if (depPort == null || depPort.length() != 3) {
      query = em.createQuery("select f from RouteInfo f "
          + "  order by f.dcity, f.acity");
    } else if (arrPort == null || arrPort.length() != 3) {
      query = em.createQuery("select f from RouteInfo f "
          + " where f.dcity = ?1 "
          + "  order by f.dcity, f.acity")
          .setParameter(1, depPort);
    } else {
      query = em.createQuery("select f from RouteInfo f "
          + " where (f.dcity = ?1 and f.acity = ?2) or (f.dcity = ?3 and f.acity = ?4)  "
          + "  order by f.id")
          .setParameter(1, depPort)
          .setParameter(2, arrPort)
          .setParameter(3, arrPort)
          .setParameter(4, depPort);
    }
    routeList = query.getResultList();

    return routeList;
  }


}
